Mega Code Archive
Statistical Functions
1) =AVERAGE(B2
2) =AVERAGEA(A2
3) =AVERAGEIF(A2
4) =AVERAGEIF(B2
5) =AVERAGEIFS(C2
6) =COUNT(A2
7) =COUNTA(A1
8) =COUNTIF(A2
9) =COUNTIF(B2
10) =COUNTIFS(B2
11) =DATEVALUE(20080223) converts text to date
12) =DATEVALUE(22-AUG-2008) converts text to date
13) =DATEVALUE(5-JUL) converts text to date
14) =EXPONDIST(0 2,10,FALSE)
15) =GAMMADIST(10,8,2,TRUE) returns the cumulative gamma distribution
16) =MAX(A2
17) =MIN(A2
18) =NORMDIST(45,37,1 3,FALSE) returns the probability mass function
19) =POISSON(3,6,FALSE) returns the poisson probability mass function with the terms above
20) =PROB(A2
21) =QUARTILE(A1
22) =STDEVA(A2
23) =WEIBULL(101,10,99,FALSE) returns thg weibull probability density function
24) =ZTEST(A2
25) AVEDEV(number1,number2, ) returns the average of the absolute deviations of data points from their mean
26) AVERAGE(number1,number2, ) returns the average (arithmetic mean)
27) AVERAGEA(value1,value2, ) returns the average of its arguments, including numbers, text, and logical values
28) CHIDIST(x,degrees_freedom) returns the one-tailed probability of the chi-squared distribution
29) CHITEST(actual_range,expected_range) returns the test for independence
30) CONFIDENCE(alpha,standard_dev,size) returns the confidence interval for a population mean
31) CORREL(array1,array2) returns the correlation coefficient between two data sets
32) COUNT(value1,value2, ) counts how many numbers are in the arguments
33) COUNTA(value1,value2, ) counts how many values are in the list of arguments
34) COUNTBLANK counts the number of blank cells within a range
35) COVAR(array1,array2) returns covariance, the average of the products of paired deviations
36) DATEVALUE(date_text) converts a text to date
37) DAY(text) converts to a day of the month
38) DAYS360(start_date,end_date,method) calculates the number of days between two dates based on a 360-day year
39) DEVSQ(number1,number2, ) returns the sum of squares of deviations
40) EXPONDIST(x,lambda,cumulative) returns the exponential distribution
41) FDIST(x,degrees_freedom1,degrees_freedom2) returns the F probability distribution
42) FINV(probability,degrees_freedom1,degrees_freedom2) returns the inverse of the F probability distribution
43) FISHER(x) returns the Fisher transformation
44) FISHERINV(y) returns the inverse of the Fisher transformation
45) FORECAST(x,Dependent, Independent) returns a value along a linear trend
46) FREQUENCY(data_array,bins_array) returns a frequency distribution as a vertical array
47) FTEST(array1,array2) returns the result of an F-test
48) GAMMADIST returns the gamma distribution
49) GAMMAINV(probability,alpha,beta) returns the inverse of the gamma cumulative distribution
50) GAMMALN(x) returns the natural logarithm of the gamma function, G(x)
51) GEOMEAN(number1,number2, ) returns the geometric mean
52) GROWTH(y,x,new_x,const) calculates predicted exponential growth by using existing data
53) HARMEAN(number1,number2, ) returns the harmonic mean
54) HYPGEOMDIST(sample_s,number_sample,population_s,number_population) returns the hypergeometric distribution
55) INTERCEPT(dependent, independent) returns the intercept of the linear regression line
56) KURT(number1,number2, ) returns the kurtosis of a data set
57) LARGE(array, Position_from_the_largest) returns the k-th largest value in a data set
58) LINEST(y,x,const,stats) returns the parameters of a linear trend
59) LOGEST returns the parameters of an exponential trend
60) LOGINV(probability,mean,standard_dev) returns the inverse of the lognormal distribution
61) LOGNORMDIST(x,mean,standard_dev) returns the cumulative lognormal distribution
62) MAX(number1,number2, ) returns the maximum value in a list of arguments
63) MEDIAN(number1,number2, ) returns the median of the given numbers
64) MIN(number1,number2, ) returns the minimum value in a list of arguments
65) MODE(number1,number2, ) returns the most common value in a data set
66) NORMDIST(x,mean,standard_dev,cumulative) returns the normal cumulative distribution
67) NORMINV(probability,mean,standard_dev) returns the inverse of the normal cumulative distribution
68) NORMSDIST(z) returns the standard normal cumulative distribution
69) NORMSINV(probability) returns the inverse of the standard normal cumulative distribution
70) PEARSON(independent, dependent) returns the Pearson product moment correlation coefficient
71) PERCENTILE(array, Percentilebetween_0_and_1) returns the k-th percentile of values in a range
72) PERCENTRANK(array,x,optional_significance) returns the percentage rank of a value in a data set
73) PERMUT(number,number_chosen) returns the number of permutations for a given number of objects
74) POISSON(x,mean,cumulative) returns the Poisson distribution
75) QUARTILE(array,quart) returns the quartile of a data set
76) RANK(number,ref,order) returns the rank of numbers
77) Return the number of blank cells in column A
78) Returns the number of empty cells on the entire worksheet named Sheet1
79) RSQ(y,x) returns the square of the Pearson product moment correlation coefficient
80) SKEW(number1,number2, ) returns the skewness of a distribution
81) SLOPE(dependent, independent) returns the slope of the linear regression line
82) SMALL(array, position) returns the k-th smallest value in a data set
83) STANDARDIZE(x,mean,standard_dev) returns a normalized value
84) STDEV(number1,number2, ) estimates standard deviation based on a sample
85) STDEVA(value1,value2, ) estimates standard deviation, including numbers, text, and logical values
86) STDEVP(number1,number2, ) calculates standard deviation, including numbers, text, and logical values
87) TDIST(x,degrees_freedom,tails) returns the Students t-distribution
88) TINV(probability,degrees_freedom) returns the inverse of the Students t-distribution
89) TREND(y,x,new_x,const) returns values along a linear trend
90) TRIMMEAN(array,percent) returns the mean of the interior of a data set
91) TTEST(array1,array2,tails,type) returns the probability associated with a Students t-test
92) Use AVERAGE with more than one range
93) Use COUNT with more than one range
94) Use the COUNTA function to return the number of nonblank cells in a range named Data
95) VAR(number1,number2, ) estimates variance based on a sample
96) VARA(value1,value2, ) estimates variance based on a sample, including numbers, text, and logical values
97) VARP(number1,number2, ) calculates variance based on the entire population
98) WEIBULL(x,alpha,beta,cumulative) returns the Weibull distribution
99) ZTEST(array, value_to_test,sigma) returns the one-tailed probability-value of a z-test